﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using WXY.Course.Model;

namespace WXY.Course.DAL
{
    public class NoticeDAL
    {
        private static SqlHelper sqlHelper = new SqlHelper(ConfigurationManager.ConnectionStrings["CourseDB"].ConnectionString);

        private const string SQL_INSERT = @"INSERT INTO TNotice(Content,CreateTime,UpdateTime,UpdateUserId,IsActive)
                                            values(@Content,@CreateTime,@UpdateTime,@UpdateUserId,0)";

        private const string SQL_UPDATE = @"UPDATE TNotice SET
                                            Content=@Content,UpdateTime=@UpdateTime,UpdateUserId=@UpdateUserId
                                            WHERE Code=@Code";

        private const string SQL_ACTIVE = @"if exists(select Code from TNotice where Code=@Code)
                                            begin
                                                UPDATE TNotice SET IsActive=0 WHERE IsActive=1 and Code<>@Code    
                                                UPDATE TNotice SET IsActive=1 WHERE Code=@Code
                                            end";

        private const string SQL_DELETE = @"DELETE FROM TNotice
                                            WHERE Code=@Code";

        private const string SQL_GET = "select * from TNotice where Code=@Code";

        private const string SQL_GETLIST = "select * from TNotice order by 1 desc";

        public static int Insert(NoticeInfo notice)
        {
            SqlParameter[] parameters ={
                new SqlParameter("@Content", notice.Content),
                new SqlParameter("@CreateTime", DateTime.Now),
                new SqlParameter("@UpdateTime", DateTime.Now),
                new SqlParameter("@UpdateUserId", notice.UpdateUser.Code),
                new SqlParameter("@IsActive", notice.IsActive),
            };

            return sqlHelper.ExecuteNonQuery(SQL_INSERT, CommandType.Text, parameters);
        }

        public static int Update(NoticeInfo notice)
        {
            SqlParameter[] parameters ={
                new SqlParameter("@Code", notice.Code),
                new SqlParameter("@Content", notice.Content),
                new SqlParameter("@UpdateTime", DateTime.Now),
                new SqlParameter("@UpdateUserId", notice.UpdateUser.Code),
                new SqlParameter("@IsActive", notice.IsActive)
            };

            return sqlHelper.ExecuteNonQuery(SQL_UPDATE, CommandType.Text, parameters);
        }
        
        public static int Active(int code)
        {
            SqlParameter[] parameters ={
                new SqlParameter("@Code", code)
            };

            return sqlHelper.ExecuteNonQuery(SQL_ACTIVE, CommandType.Text, parameters);

        }

        public static int Delete(int code)
        {
            SqlParameter[] parameters ={
                new SqlParameter("@Code", code)
            };

            return sqlHelper.ExecuteNonQuery(SQL_DELETE, CommandType.Text, parameters);
        }

        /// <summary>
        /// 获取指定Code的模块信息
        /// </summary>
        /// <returns></returns>
        public static NoticeInfo Get(int code)
        {
            SqlParameter parameter = new SqlParameter("@Code", code);

            return sqlHelper.ExecuteQuery(SQL_GET, CommandType.Text, (reader) =>
            {
                NoticeInfo notice = null;
                while (reader.Read())
                {
                    notice = new NoticeInfo();
                    notice.Code = Convert.ToInt32(reader["Code"]);
                    notice.Content = reader["Content"].ToString();
                    notice.CreateTime = Convert.ToDateTime(reader["CreateTime"]);
                    notice.UpdateTime = Convert.ToDateTime(reader["UpdateTime"]);
                    notice.UpdateUser = AccountDAL.Get(Convert.ToInt32(reader["UpdateUserId"]));
                    notice.IsActive = Convert.ToBoolean(reader["IsActive"]);
                }
                return notice;
            }, parameter);
        }

        /// <summary>
        /// 获取模块列表
        /// </summary>
        public static IList<NoticeInfo> GetList()
        {
            return sqlHelper.ExecuteQuery(SQL_GETLIST, CommandType.Text, (reader) =>
            {
                IList<NoticeInfo> list = new List<NoticeInfo>();
                while (reader.Read())
                {
                    NoticeInfo notice = new NoticeInfo();
                    notice.Code = Convert.ToInt32(reader["Code"]);
                    notice.Content = reader["Content"].ToString();
                    notice.CreateTime = Convert.ToDateTime(reader["CreateTime"]);
                    notice.UpdateTime = Convert.ToDateTime(reader["UpdateTime"]);
                    notice.UpdateUser = AccountDAL.Get(Convert.ToInt32(reader["UpdateUserId"]));
                    notice.IsActive = Convert.ToBoolean(reader["IsActive"]);
                    list.Add(notice);
                }
                return list;
            }, null);
        }
    }
}
